const { table } = require('console');
var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
function getTable_Weight(table,rows){
    for(var i=0;i<rows.length;i++){
        for(var x in rows[i]){
            if(x!='Avg'){
                if(rows[i][x]==0.1)
                    table[x][0]+=rows[i]['Avg']
                if(rows[i][x]==0.5)
                    table[x][1]+=rows[i]['Avg']
                if(rows[i][x]==0.9)
                    table[x][2]+=rows[i]['Avg']
            }
        }
    }
    for(x in table){
        var sum = table[x][0]+table[x][1]+table[x][2];
        for(var i=0;i<3;i++){
            table[x][i] = (table[x][i]/sum).toFixed(2);
        }
    }
    return table
}
function getTable_noWeight(table,rows){
    for(var i=0;i<rows.length;i++){
        for(var x in rows[i]){
            if(x!='Avg'){
                if(rows[i][x]==0.1)
                    table[x][0]+=1
                if(rows[i][x]==0.5)
                    table[x][1]+=1
                if(rows[i][x]==0.9)
                    table[x][2]+=1
            }
        }
    }
    for(x in table){
        var sum = table[x][0]+table[x][1]+table[x][2];
        for(var i=0;i<3;i++){
            table[x][i] = (table[x][i]/sum).toFixed(2);
        }
    }
    return table
}
router.get('/', function (req, res, next) {
    var db = new sqlite3.Database(path.join(__dirname, "../db/Delphi.s3db"));
    try {
        if(req.query.question=='2-1'){
            if(req.query.weight=='true'){
                sqlStr1 = 'select n21,n14,n7,Avg from answer2_1 join weight on weight.[expert_id] = answer2_1.[expert_id] and q_no = weight.[question]'
                db.all(sqlStr1, function (err, rows) {
                    if(rows.length!=0){
                        var table = {"n21":[0,0,0],"n14":[0,0,0],"n7":[0,0,0]};
                        table = getTable_Weight(table,rows)
                        console.log(table)
                        res.status(200).json(table);
                    }
                });
            }else{
                sqlStr1 = 'select * from standard_2_1'
                db.all(sqlStr1, function (err, rows) {
                    if(rows.length!=0){
                        var table = {"n21":[],"n14":[],"n7":[]};
                        for(var i=0;i<3;i++){
                            table.n21[i] = rows[i].z_n21;
                            table.n14[i] = rows[i].z_n14;
                            table.n7[i] = rows[i].z_n7;
                        }
                        res.status(200).json(table);
                    }
                });
            }
        }
        else if(req.query.question=='2-2'){
            if(req.query.weight=='true'){
                sqlStr = "select g147,g77,g07,gno,Avg from answer2_2 join weight on weight.[expert_id] = answer2_2.[expert_id] and q_no = weight.[question]"
                db.all(sqlStr, function (err, rows) {
                    if(rows.length!=0){
                        var table = {"g147":[0,0,0],"g77":[0,0,0],"g07":[0,0,0],"gno":[0,0,0]};
                        table = getTable_Weight(table,rows)
                        console.log(table)
                        res.status(200).json(table);
                    }
                });
            }else{
                sqlStr  = 'select * from standard_2_2';
                db.all(sqlStr, function (err, rows) {
                    if(rows.length!=0){
                        var table = {"g147":[],"g77":[],"g07":[],"gno":[]};
                        for(var i=0;i<3;i++){
                            table.g147[i] = rows[i].g147;
                            table.g77[i] = rows[i].g77;
                            table.g07[i] = rows[i].g07;
                            table.gno[i] = rows[i].gno;
                        }
                        res.status(200).json(table);
                    }
                });
            }
        }
        else{
            if(req.query.weight=="true"){
                sqlStr="select byes,b10,b11,b12,bno,Avg from answer3 join weight on weight.[expert_id] = answer3.[expert_id] and q_no = weight.[question]"
                db.all(sqlStr,function(err,rows){
                    if(rows.length!=0){
                        var table = {"byes":[0,0,0],"b10":[0,0,0],"b11":[0,0,0],"b12":[0,0,0],"bno":[0,0,0]};
                        table = getTable_Weight(table,rows);
                        console.log(table)
                        res.status(200).json(table);
                    }
                })
            }else{
                sqlStr = "select byes,b10,b11,b12,bno from answer3"
                db.all(sqlStr,function(err,rows){
                    if(rows.length!=0){
                        var table = {"byes":[0,0,0],"b10":[0,0,0],"b11":[0,0,0],"b12":[0,0,0],"bno":[0,0,0]};
                        table = getTable_noWeight(table,rows);
                        console.log(table)
                        res.status(200).json(table);
                    }
                })
            }
            
        }
    } catch (ex) {
        console.log(ex);
    }
});
module.exports = router;